package mythmultibatchparser;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import datamodel.Entry;

/**
 * Connects to the MythTV database, gets entries in it and updates the entries
 * once the program is done.
 * 
 */
public class MythDatabase {
	/** The database connection to the mysql server */
	Connection conn = null;

	/** The user name for the database access */
	String username;

	/** The password for the database access */
	String password;

	/** The ip address for the database access */
	String ip;

	/** The title to a certain show or movie */
	String titleName;

	/** Whether or not to only work with new database entries */
	Boolean all;

	/** Whether or not to force a 4:3 ratio for entries */
	Boolean force43;

	/**
	 * Constructor
	 * 
	 * @param username
	 *            The user name for the database access
	 * @param password
	 *            The password for the database access
	 * @param ip
	 *            The ip address for the database access
	 * @param titleName
	 *            The title to a certain show or movie
	 * @param all
	 *            Whether or not to only work with new database entries
	 * @param force43
	 *            Whether or not to force a 4:3 ratio for entries
	 */
	public MythDatabase( String username, String password, String ip, String titleName, Boolean all, Boolean force43 ) {
		super();
		this.username = username;
		this.password = password;
		this.ip = ip;
		this.titleName = titleName;
		this.all = all;
		this.force43 = force43;
	}

	/**
	 * Updates all of the entries in the Vector
	 * 
	 * @param entries
	 *            The entries to update
	 */
	public void updateEntries( Vector<Entry> entries ) {
		StringBuilder command = new StringBuilder();

		try {
			Statement s = conn.createStatement();

			for ( Entry entry : entries ) {
				if ( entry.isChanged() == false ) {
					continue;
				}

				command = new StringBuilder();
				command.append( "update videometadata set " );
				command.append( "title=\"" + escapeSQL( entry.getTitle() ) + "\", " );
				command.append( "director=\"" + escapeSQL( entry.getDirector() ) + "\"," );
				command.append( "plot=\"" + escapeSQL( entry.getPlot() ) + "\"," );
				command.append( "rating=\"" + entry.getRating() + "\", " );
				command.append( "inetref=\"" + entry.getInetref() + "\", " );
				command.append( "year=" + entry.getYear() + ", " );
				command.append( "userrating=" + entry.getUserrating() + "," );
				command.append( "length=" + entry.getLength() + ", " );
				command.append( "showlevel=" + entry.getShowlevel() + ", " );
				command.append( "filename=\"" + entry.getFilename() + "\", " );
				command.append( "coverfile=\"" + entry.getCoverfile() + "\", " );
				command.append( "childid=" + entry.getChilid() + ", " );

				if ( entry.getPlaycommand() != null ) {
					command.append( "playcommand=\"" + entry.getPlaycommand() + "\", " );
				}

				command.append( "browse=" + entry.isBrowse() + ", " );
				command.append( "category=" + entry.getCategory() );
				command.append( " where intid=" + entry.getIntid() );

				s.executeUpdate( command.toString() );
			}

			s.close();
		} catch ( Exception e ) {
			e.printStackTrace();
			System.err.println( "Could not run update on videometadata, command was " + command.toString() );
		}
	}

	/**
	 * Escapes out the ' and " characters.
	 * 
	 * @param string
	 *            The string to escape
	 * @return The escaped string
	 */
	private String escapeSQL( String string ) {
		string = string.replace( "\'", "\\\'" );
		string = string.replace( "\"", "\\\"" );

		return string;
	}

	/**
	 * Gets all of the database entries that conform to the parameters passed in
	 * 
	 * @return A vector of entries that conform to the parameters passed in
	 */
	public Vector<Entry> getEntries() {
		Vector<Entry> entries = new Vector<Entry>();

		try {
			Statement s = conn.createStatement();

			String query = "select * from videometadata ";

			if ( all.equals( Boolean.FALSE ) && titleName != null ) {
				// They want new ones with a specific title
				query += " where inetref = 00000000 and plot=\"None\" and title like \'%" + titleName + "%\'";
			} else if ( titleName != null ) {
				// They want ones with a specific title
				query += " where title like \'%" + titleName + "%\'";
			}

			query += " order by title";

			s.executeQuery( query );
			ResultSet rs = s.getResultSet();

			int count = 0;

			while ( rs.next() ) {
				Entry entry = new Entry( rs.getInt( "intid" ), rs.getString( "title" ), rs.getString( "director" ), rs
						.getString( "plot" ), rs.getString( "rating" ), rs.getString( "inetref" ), rs.getInt( "year" ),
						rs.getFloat( "userrating" ), rs.getInt( "length" ), rs.getInt( "showlevel" ), rs
								.getString( "filename" ), rs.getString( "coverfile" ), rs.getInt( "childid" ), rs
								.getString( "playcommand" ), rs.getBoolean( "browse" ), rs.getInt( "category" ) );

				// Add the 4:3 ratio forcing
				if ( force43.equals( Boolean.TRUE ) ) {
					entry.setPlaycommand( "mplayer -aspect 4:3 -fs" );
				}

				entries.add( entry );

				count++;
			}

			rs.close();
			s.close();
			System.out.println( count + " rows were retrieved for parsing" );
		} catch ( Exception e ) {
			System.err.println( "Could not run query on videometadata" );
			System.exit( -1 );
		}

		return entries;
	}

	/**
	 * Connects via JDBC to the MythTV database
	 */
	public void connectToDatabase() {
		try {

			String url = "jdbc:mysql://" + ip + "/mythconverg";
			Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
			conn = DriverManager.getConnection( url, username, password );
			System.out.println( "Database connection established" );
		} catch ( Exception e ) {
			System.err.println( "Cannot connect to database server" );
			System.exit( -1 );
		}
	}

	/**
	 * Closes the connection to the MythTV database
	 */
	public void closeConnection() {
		if ( conn != null ) {
			try {

				conn.close();
				// System.out.println( "Database connection closed" );
			} catch ( Exception e ) { /* ignore close errors */
			}
		}
	}
}
